﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
//using Oracle.DataAccess.Client;
//using Oracle.DataAccess.Types;
//using Npgsql;

namespace IntelligentCMS
{
    /// <summary>
    /// The DbDataHelper class, for any additional settings or methods we need to help get data in and out of a
    /// particular type of database.
    /// </summary>
    public class CMSDbDataHelper
    {
        // We will store the dbconnectionstring in the web.config file when we are ready for prime time.
        public CMSDbDataHelper(string sqlquery, string dbtype, string dbconnectionstring)
        {

            this.sqlquery = sqlquery;
            this.dbtype = dbtype;
            this.dbconnectionstring = dbconnectionstring;


        }

        private string dbconnectionstring = string.Empty;
        private string sqlquery = string.Empty;
        private DataTable data = new DataTable();
        private bool selectquery = true;
        private string dbtype = string.Empty;
        public const string Oracle = "Oracle";
        public const string Microsoft = "Microsoft";
        public const string PostgreSQL = "PostgreSQL";
        public const string MySQL = "MySQL";
        public const string XML = "XML";

        /// <summary>
        /// Data coming out of the database.
        /// </summary>
        public DataTable Data  {
            get 
            {
                return data;
            }

        }

        /// <summary>
        /// Boolean for whether or not the query is a nonquery (Insert, Update, Drop, etc.)
        /// </summary>
        public bool SelectQuery
        {
            set
            {
                selectquery = value;
            }
        }


        /// <summary>
        /// Executes the helper, compiling information and custom strings for connecting to a particular db.
        /// </summary>
        public void Execute()
        {

            switch (dbtype)
            {
                case Oracle:
                    //Helper_Oracle();
                    break;
                case Microsoft:
                    Helper_Microsoft();
                    break;
                case PostgreSQL:
                    //Helper_PostgreSQL();
                    break;
                case MySQL:
                    // Todo: MySQL support is kind of shaky for .NET. Needs more research.
                    Helper_MySQL();
                    break;
                case XML:
                    // Todo: Linq will be used here, since Linq queries can be easily translated from SQL queries. 
                    Helper_XML();
                    break;
                default:
                    //No helper needed.
                    break;

            }

        }
        /*
        /// <summary>
        /// Oraclehelper -> for Oracle databases.
        /// </summary>
        private void Helper_Oracle()
        {
            // Need an Oracle database to test this against;
            OracleConnection newConnection = new OracleConnection(dbconnectionstring);
            OracleCommand newCommand = new OracleCommand(sqlquery);
            newCommand.Connection = newConnection;

            try
            {
                newConnection.Open();
                if (nonquery)
                {
                    newCommand.ExecuteNonQuery();
                }
                else
                {

                    OracleDataAdapter newAdapter = new OracleDataAdapter(newCommand);
                    newAdapter.Fill(data);
                }


            }
            finally
            {
                newConnection.Close();
            }


        }
        */
        /// <summary>
        /// Mshelper -> for Microsoft databases.
        /// </summary>
        private void Helper_Microsoft()
        {
            SqlConnection newConnection = new SqlConnection(dbconnectionstring);
            SqlCommand newCommand = new SqlCommand(sqlquery);
            newCommand.Connection = newConnection;

            try
            {
                newConnection.Open();
                if (!selectquery)
                {
                    newCommand.ExecuteNonQuery();
                }
                else
                {
                    SqlDataAdapter newAdapter = new SqlDataAdapter(newCommand);
                    newAdapter.Fill(data);
                }

            }
            finally
            {
                newConnection.Close();
            }



        }


/*
        /// <summary>
        /// Helper for PostgreSQL databases.
        /// </summary>
        private void Helper_PostgreSQL()
        {

            
            NpgsqlConnection newConnection = new NpgsqlConnection(dbconnectionstring);
            NpgsqlCommand newCommand = new NpgsqlCommand(sqlquery);
            newCommand.Connection = newConnection;

            try
            {
                newConnection.Open();
                if (nonquery)
                {
                    newCommand.ExecuteNonQuery();
                }
                else
                {
                    NpgsqlDataAdapter newAdapter = new NpgsqlDataAdapter(newCommand);
                    newAdapter.Fill(data);
                }

            }
            finally
            {
                newConnection.Close();
            }


        }
*/
        /// <summary>
        /// Helper for MySQL databases.
        /// </summary>
        private void Helper_MySQL()
        {

        }

        

        /// <summary>
        /// Helper for XML.
        /// </summary>
        private void Helper_XML()
        {


        }

    }
}
